<?php
namespace Service\Library;

use Pdo;
use Exception;
use RuntimeException;
use PdoException;

class LibPdo extends Pdo
{
    const TIME_OUT = 5;

    private $errno = 0;
    private $error = '';
    private $database;
    private $table;

    public static $mysql_conn = [];

    public function setError($errno, $error)
    {
        $this->errno = $errno;
        $this->error = $error;
    }

    public function getLastErrorInfo()
    {
        return ['errno' => $this->errno, 'error' => $this->error];
    }

    public function getLastErrno()
    {
        return $this->errno;
    }

    public function getLastError()
    {
        return $this->error;
    }

    public function setPdoDT($table, $database = null)
    {
        $this->table = $table;
        $database && $this->database = $database;
    }

    /**
     * !!!注意 如果手动调用该方法 请再次调用setPdoDT 方法设置 database 和 table
     * 初始化Pdo对象
     * @param $class_name string model class名
     * @param $hash_key string 需要散列的hash_key 默认null
     * @param $charset string 默认utf8mb4
     * @return object
     */
    public static function initPdo($host, $port, $user, $pwd, $charset)
    {
        $conn_name = "{$host}:{$port}";
        $Pdo = isset(self::$mysql_conn[$conn_name]) ? self::$mysql_conn[$conn_name] : null;
        if ($Pdo === null) {
            try {
                $dsn = "mysql:host={$host};port={$port};";
                $Pdo = new self($dsn, $user, $pwd, [
                    Pdo::ATTR_TIMEOUT => self::TIME_OUT,
                    Pdo::MYSQL_ATTR_INIT_COMMAND => "SET NAMES '{$charset}'",
                ]);
                //$Pdo->mysql_config = $mysql_config;
                $Pdo->dsn = $dsn;
                $Pdo->setAttribute(Pdo::ATTR_ERRMODE, Pdo::ERRMODE_EXCEPTION);
                self::$mysql_conn[$conn_name] = $Pdo;
            } catch (PdoException $e) {
                die('Connection failed - Code:' . $e->getCode() . ' Message:' . $e->getMessage());
            }
        }

        return $Pdo;
    }

    private function parseSql($sql)
    {
        return preg_replace('/<DB.TABLE>/', "`$this->database`.`$this->table`", $sql);
    }

    private function buildStmtExecute($sql, $param = [], $action = 'exec')
    {
        $start_time = microtime(true);
        $result = false;
        try {
            $sql = $this->parseSql($sql);
           //echo $sql;exit;
            if (is_array($param) && $param) {
                $stmt = $this->prepare($sql);
                $stmt->execute(array_values($param));
            } else {
                $stmt = $this->query($sql);
            }
            switch ($action) {
                case 'exec':
                    $result = $this->checkData($stmt->rowCount());
                    break;
                case 'query':
                    $result = $this->checkData($stmt->fetchAll(Pdo::FETCH_ASSOC));
                    break;
                case 'query_one':
                    $result = $this->checkData($stmt->fetch(Pdo::FETCH_NUM));
                    break;
                default:
                    $result = $this->checkData($stmt->rowCount());
                    break;
            }
        } catch (PdoException $e) {
            //throw new PdoException($e->getCode(), $e->getMessage() . "SQL: $sql");
            echo $sql;
        } finally {
            $option = strtoupper(substr($sql, 0, strpos($sql, ' ')));
            $end_time = microtime(true) - $start_time;
            $error_code = $result === false ? $e->getCode() : 0;
            $error_info = $result === false ? $e->getMessage() : '';
            $str_result = json_encode($result);
            $str_params = json_encode($param);
            $msg = '';
            if ($error_code && $error_info) {
                $msg = "error_code:{$error_code} error_info:{$error_info}";
            }
            $msg .= " sql:{$sql} params:{$str_params} rs:{$str_result}";
        }

        return $result;
    }

    private function checkData($data)
    {
        if ($data === false) {
            $error = $this->errorInfo();
            if ($error[1]) {
                $this->setError($error[1], $error[2]);
            }
        }
        return $data;
    }

    public function exec_with_prepare($sql, $param = [])
    {
        return $this->buildStmtExecute($sql, $param, 'exec');
    }

    public function query_with_prepare($sql, $param = [], $fetch = Pdo::FETCH_ASSOC)
    {
        return $this->buildStmtExecute($sql, $param, 'query');
    }

    public function query_one_with_prepare($sql, $param = [])
    {
        return $this->buildStmtExecute($sql, $param, 'query_one');
    }

    public function count($where = [], $order = null, $offset = 0, $limit = 0)
    {
        $where = $this->parseWhere($where);
        $sql = "SELECT COUNT(1) FROM <DB.TABLE> WHERE {$where['where']}";
        $order = $this->parseOrder($order);
        if (!empty($order)) $sql .= " {$order}";
        if ($limit > 0) $sql .= " LIMIT {$offset},{$limit}";
        $result = $this->query_one_with_prepare($sql, $where['params']);
        return (is_array($result) && isset($result[0])) ? (int)$result[0] : 0;
    }

    public function sum($col, $where = [])
    {
        $where = $this->parseWhere($where);
        $sql = "SELECT SUM(`{$col}`) FROM <DB.TABLE> WHERE {$where['where']}";
        $result = $this->query_one_with_prepare($sql, $where['params']);
        return (is_array($result) && isset($result[0])) ? (int)$result[0] : 0;
    }

    /**
     * 解析转换where条件
     * @param array $where
     * @example $where = [['id', '=', '1'], ['age', '>', '50']]
     * @example $where = ['id', 'in', [1, 2, 3]];
     * @example $where = ['name', 'like', '%a']
     * @return array
     */
    public function parseWhere($where)
    {
        $ret = [];
        $a_op = ['=', '!=', '<>', '<', '>', '<=', '>=', 'in', 'notin', 'like'];
        if (is_array($where) && !empty($where)) {
            if (count($where) === 3 && isset($where[1]) && !is_array($where[1]) && in_array(trim($where[1]), $a_op)) {
                $where = [$where];
            }

            foreach ($where as $key => $value) {
                // 统一 $where 的数据格式为 [[$k1, $op1, $v1], [$k1, $op1, $v1]]
                if (is_string($key)) {
                    if (is_array($value)) {
                        $value = [$key, 'in', $value];
                    } else {
                        $value = [$key, '=', $value];
                    }
                }

                if (count($value) != 3) {
                    continue;
                }

                $value[0] = $this->quoteColumnName($value[0]);
                $value[1] = trim($value[1]);
                if (in_array($value[1], ['in', 'notin'])) {
                    // 数组需要转义
                    if (is_array($value[2])) {
                        if ($value[2]) {
                            foreach ($value[2] as &$v2) {
                                $v2 = $this->quoteValue($v2);
                            }
                            $value[2] = implode(',', $value[2]);
                        } else {
                            $value[2] = $this->quoteValue('');
                        }
                    } else {
                        $value[2] = $this->quoteValue($value[2]);
                    }
                }

                switch (strtolower($value[1])) {
                    case 'in':
                        $ret['where'][] = " {$value[0]} IN ({$value[2]}) ";
                        $ret['params'][] = $value[2];
                        break;
                    case 'notin':
                        $ret['where'][] = " {$value[0]} NOT IN ({$value[2]}) ";
                        break;
                    case 'like':
                        $ret['where'][] = " {$value[0]} LIKE ? ";
                        $ret['params'][] = $value[2];
                        break;
                    default:
                        $ret['where'][] = " {$value[0]} {$value[1]} ? ";
                        $ret['params'][] = $value[2];
                }
            }
        }

        if (empty($ret) || empty($ret['where'])) {
            $ret['where'] = '1';
            $ret['params'] = [];
        } else {
            $ret['where'] = implode(' AND ', $ret['where']);
        }
        return $ret;
    }

    /**
     * 解析order规则 支持string 和 数组2种形式
     * @param string $order id DESC,age ASC
     *        array  $order array('id' => 'DESC' , 'age' => 'ASC')
     * @return string
     */
    public function parseOrder($order = null)
    {
        $ret = '';
        if (!empty($order)) {
            if (!is_array($order)) {
                $ret = " ORDER BY {$order}";
            } else {
                $temp = [];
                $sort_rule = ['ASC' => 1, 'DESC' => 1];
                foreach ($order as $key => $value) {
                    if (isset($sort_rule[strtoupper($value)])) {
                        $temp[] = " `{$key}` {$value} ";
                    }
                }
                if (!empty($temp)) {
                    $ret = ' ORDER BY ' . implode(",", $temp);
                }
            }
        }
        return $ret;
    }

    /**
     * 解析字段列表规则 支持string 和 数组2种形式
     * @param string $fields = id,name,sex 字段列表
     *        array  $fields array('id' ， 'name' , 'sex')
     * @return string
     */
    public function parseFields($fields = null)
    {
        $ret = '';
        if (!empty($fields)) {
            if (is_array($fields)) {
                $temp = [];
                foreach ($fields as $key => $value) {
                    $temp[] = $this->quoteColumnName($value);
                }
                if (!empty($temp)) {
                    $ret = implode(",", $temp);
                }
            } else {
                $ret = $fields;
            }
        }
        return $ret;
    }

    public function quoteColumnName($name)
    {
        return strpos($name, '`') !== false || $name === '*' ? $name : "`$name`";
    }

    /**
     * Quotes a string value for use in a query.
     * @param string $str string to be quoted
     * @return string the properly quoted string
     * @see http://www.php.net/manual/en/function.Pdo-quote.php
     */
    public function quoteValue($str)
    {
        if (!is_string($str)) {
            return $str;
        }

        if (($value = $this->quote($str)) !== false) {
            return $value;
        } else {
            // 驱动不支持 (e.g. oci)
            return "'" . addcslashes(str_replace("'", "''", $str), "\000\n\r\\\032") . "'";
        }
    }

    /**
     * 插入
     * @param array $param = array('username' => '123' , 'password' => 'bbbb');
     * @param array $update = array('id' => '1','username' => 'bbb') 如主键id-1的数据存在 将该数据的username更新
     * @return mixed 如果有自增ID会返回last insert id 没有返回执行的结果
     */
    public function insert($param, $update = [])
    {
        is_array($param) || $param = [];
        $values = [];
        foreach ($param as $key => $val) $values[] = '?';
        $insert_keys = [];
        if (is_array($param)) {
            foreach ($param as $key => $val) {
                $insert_keys[] = "`{$key}`";
            }
        }
        $update_keys = [];
        if (is_array($update)) {
            foreach ($update as $key => $val) {
                $update_keys[] = "`$key` = ?";
                $param[] = $val;
            }
        }
        $sql = 'INSERT INTO <DB.TABLE> (' . implode(', ', $insert_keys) . ') VALUES (' . implode(', ', $values) . ')';
        !empty($update_keys) && $sql .= ' ON DUPLICATE KEY UPDATE ' . implode(', ', $update_keys);
        $ret = $this->exec_with_prepare($sql, $param);
        if ($ret !== false) {
            $insert_id = $this->lastInsertId();
            if ($insert_id) {
                $ret = $insert_id;
            }
        }
        return $ret !== false ? $ret : false;
    }

    /**
     * 更新
     * @param array $param = array('username' => '123' , 'password' => 'bbbb');
     * @param array $where = array('a','=','1') or array(array('username','=','abc'),array('age','>',15))
     * @param int $limit
     * @return mixed
     */
    public function update($param, $where = [], $limit = 0)
    {
        is_array($param) || $param = [];
        $update_keys = [];
        foreach ($param as $key => $val) {
            $update_keys[] = "`$key` = ?";
        }
        $where = $this->parseWhere($where);
        $sql = 'UPDATE <DB.TABLE> SET ' . implode(', ', $update_keys) . " WHERE {$where['where']}";
        if ($limit > 0) {
            $sql .= " LIMIT {$limit}";
        }
        $sql_params = array_merge(array_values($param), is_array($where['params']) ? array_values($where['params']) : []);
        $ret = $this->exec_with_prepare($sql, $sql_params);
        return $ret !== false ? true : false;
    }

    /**
     * 删除数据
     * @param array $where = array('a','=','1') or array(array('username','=','abc'),array('age','>',15))
     * @return bool
     */
    public function delete($where = [], $limit = 0, $order = null)
    {
        if (!is_array($where) || empty($where)) return false;
        $where = $this->parseWhere($where);
        $sql = "DELETE FROM <DB.TABLE> WHERE {$where['where']}";
        $order = $this->parseOrder($order);
        if (!empty($order)) $sql .= " {$order}";
        if ($limit > 0) $sql .= " LIMIT {$limit}";
        $sql_params = is_array($where['params']) ? array_values($where['params']) : [];
        $ret = $this->exec_with_prepare($sql, $sql_params);
        return $ret !== false ? true : false;
    }

    public function updateById($id, $param)
    {
        return $this->update($param, ['id', '=', $id]);
    }

    public function deleteById($id)
    {
        return $this->delete(['id', '=', $id]);
    }

    public function deleteInId($where = [])
    {
        return $this->delete($where);
    }

    /**
     * @param array $where
     * @param null $order
     * @param int $offset
     * @param int $limit
     * @param string|array $fields
     * @param null $fore_index
     * @return array|bool|mixed
     */
    public function findAll($where = [], $order = null, $offset = 0, $limit = 0, $fields = '', $fore_index = null)
    {
        $where = $this->parseWhere($where);
        if ($fore_index) {
            $sql = "SELECT * FROM  <DB.TABLE> FORCE INDEX($fore_index) WHERE {$where['where']}";
        } else {
            $sql = "SELECT * FROM <DB.TABLE> WHERE {$where['where']}";
        }

        $fields = $this->parseFields($fields);
        if (!empty($fields)) {
            $sql = "SELECT {$fields} FROM <DB.TABLE> WHERE {$where['where']}";
        }
        $order = $this->parseOrder($order);
        if (!empty($order)) $sql .= " {$order}";
        if ($limit > 0) $sql .= " LIMIT {$offset},{$limit}";
        $sql_params = (isset($where['params']) && is_array($where['params'])) ? array_values($where['params']) : [];
        return $this->query_with_prepare($sql, $sql_params);
    }

    public function find($where = [], $order = null, $fields = [])
    {
        $ret = $this->findAll($where, $order, 0, 1, $fields);
        return $ret === false ? $ret : (isset($ret[0]) ? $ret[0] : []);
    }

    public function findById($id, $order = null, $fields = [])
    {
        return $this->find(['id', '=', $id], $order, $fields);
    }

    public function save($data)
    {
        return $this->insert($data, $data);
    }

    /**
     * @param $param array $params = array('num' => 1)
     * @param $where array $where = array('a','=','1') or array(array('username','=','abc'),array('age','>',15))
     * @return bool
     */
    public function increment($param, $where)
    {
        is_array($param) || $param = [];
        $update_keys = [];
        foreach ($param as $key => $val) {
            $val = (int)$val;
            $update_keys[] = "`$key` = `$key` + $val";
        }
        $where = $this->parseWhere($where);
        $sql = 'UPDATE <DB.TABLE> SET ' . implode(', ', $update_keys) . " WHERE {$where['where']}";
        $sql_params = is_array($where['params']) ? array_values($where['params']) : [];
        $ret = $this->exec_with_prepare($sql, $sql_params);
        return $ret !== false ? true : false;
    }

    /**
     * @param $param array $params = array('num' => 1)
     * param $update_param array $update_param = array('updated_time' => 2212212121) 这个数组是直接更新
     * @param $where array $where = array('a','=','1') or array(array('username','=','abc'),array('age','>',15))
     * @return bool
     */
    public function incrementByFloat($param, $where, $update_param = [])
    {
        is_array($param) || $param = [];
        $update_keys = [];
        foreach ($param as $key => $val) {
            $val = (double)$val;
            $update_keys[] = "`$key` = `$key` + $val";
        }
        foreach ($update_param as $key => $val) {
            $update_keys[] = "`$key` = '$val'";
        }

        $where = $this->parseWhere($where);
        $sql = 'UPDATE <DB.TABLE> SET ' . implode(', ', $update_keys) . " WHERE {$where['where']}";
        $sql_params = is_array($where['params']) ? array_values($where['params']) : [];
        return $this->exec_with_prepare($sql, $sql_params);
    }

    /**
     * @param $param array $params = array('num' => 1)
     * @param $where array $where = array('a','=','1') or array(array('username','=','abc'),array('age','>',15))
     * @return bool
     */
    public function decrement($param, $where)
    {
        is_array($param) || $param = [];
        $update_keys = [];
        foreach ($param as $key => $val) {
            $val = (int)$val;
            $update_keys[] = "`$key` = `$key` - $val";
        }
        $where = $this->parseWhere($where);
        $sql = 'UPDATE <DB.TABLE> SET ' . implode(', ', $update_keys) . " WHERE {$where['where']}";
        $sql_params = is_array($where['params']) ? array_values($where['params']) : [];
        $ret = $this->exec_with_prepare($sql, $sql_params);
        return $ret !== false ? true : false;
    }

    /**
     * @param $param array $params = array('num' => 1)
     * @param $where array $where = array('a','=','1') or array(array('username','=','abc'),array('age','>',15))
     * @return bool
     */
    public function decrementByFloat($param, $where,$update_param = [])
    {
        is_array($param) || $param = [];
        $update_keys = [];
        foreach ($param as $key => $val) {
            $val = (double)$val;
            $update_keys[] = "`$key` = `$key` - $val";
        }
        foreach ($update_param as $key => $val) {
            $update_keys[] = "`$key` = '$val'";
        }
        $where = $this->parseWhere($where);
        $sql = 'UPDATE <DB.TABLE> SET ' . implode(', ', $update_keys) . " WHERE {$where['where']}";
        $sql_params = is_array($where['params']) ? array_values($where['params']) : [];
        $ret = $this->exec_with_prepare($sql, $sql_params);
        return $ret !== false ? true : false;
    }

}